import os
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter


def detect_title_row(df):
    """
    检测数据框中的标题行
    如果第一行只有一列有数据，则认为这是标题行
    """
    first_row = df.iloc[0]
    non_empty_cells = first_row.notna().sum()

    # 如果第一行只有一个非空单元格，且该单元格不为空字符串
    if (
        non_empty_cells == 1
        and pd.notna(first_row.iloc[0])
        and str(first_row.iloc[0]).strip() != ""
    ):
        return True, first_row.iloc[0]
    return False, None


def apply_regular_formatting(worksheet, df, has_title=False, title_text=""):
    """
    应用常规Excel格式美化
    """
    # 定义常规样式
    title_font = Font(bold=True, size=14)
    header_font = Font(bold=True, size=11)
    data_font = Font(size=10)

    alignment_center = Alignment(horizontal="center", vertical="center")
    alignment_left = Alignment(horizontal="left", vertical="center")

    # 浅灰色背景用于表头
    header_fill = PatternFill(
        start_color="F0F0F0", end_color="F0F0F0", fill_type="solid"
    )

    # 细边框
    thin_border = Border(
        left=Side(style="thin"),
        right=Side(style="thin"),
        top=Side(style="thin"),
        bottom=Side(style="thin"),
    )

    # 设置行高
    # 标题行更高
    if has_title:
        worksheet.row_dimensions[1].height = 30
        # 合并标题单元格
        worksheet.merge_cells(
            start_row=1, start_column=1, end_row=1, end_column=len(df.columns)
        )
        title_cell = worksheet.cell(row=1, column=1)
        title_cell.value = title_text
        title_cell.font = title_font
        title_cell.alignment = alignment_center
        title_cell.border = thin_border

        # 表头从第二行开始
        header_row = 2
        data_start_row = 3
    else:
        header_row = 1
        data_start_row = 2

    # 设置表头行高和样式
    worksheet.row_dimensions[header_row].height = 25

    # 处理表头
    for col_num, column_title in enumerate(df.columns, 1):
        cell = worksheet.cell(row=header_row, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = alignment_center
        cell.border = thin_border

    # 设置数据行高和样式
    for row_num in range(data_start_row, len(df) + data_start_row):
        worksheet.row_dimensions[row_num].height = 20  # 增加行高
        for col_num in range(1, len(df.columns) + 1):
            cell = worksheet.cell(row=row_num, column=col_num)
            cell.font = data_font
            cell.alignment = alignment_left
            cell.border = thin_border

    # 自动调整列宽
    for column in worksheet.columns:
        max_length = 0
        column_letter = get_column_letter(column[0].column)

        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass

        adjusted_width = min(max_length + 3, 50)  # 稍微增加列宽
        worksheet.column_dimensions[column_letter].width = adjusted_width

    # 冻结窗格
    if has_title:
        worksheet.freeze_panes = "A3"  # 冻结标题和表头
    else:
        worksheet.freeze_panes = "A2"  # 冻结表头


def merge_excel_files_regular(output_folder, output_filename="合并文件.xlsx"):
    """
    将多个Excel文件合并到一个Excel文件的不同Sheet中，使用常规样式
    """
    try:
        if not os.path.exists(output_folder):
            print(f"错误：文件夹 '{output_folder}' 不存在")
            return False

        # 获取所有Excel文件
        excel_files = [
            f
            for f in os.listdir(output_folder)
            if f.endswith((".xlsx", ".xls")) and not f.startswith("~")
        ]

        if not excel_files:
            print(f"在文件夹 '{output_folder}' 中没有找到Excel文件")
            return False

        print(f"找到 {len(excel_files)} 个Excel文件:")
        for file in excel_files:
            print(f"  - {file}")

        output_path = os.path.join('output', output_filename)

        # 如果输出文件已存在，先删除
        if os.path.exists(output_path):
            os.remove(output_path)

        # 创建新的工作簿
        with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
            workbook = writer.book

            for excel_file in excel_files:
                file_path = os.path.join(output_folder, excel_file)

                try:
                    # 读取数据
                    df = pd.read_excel(file_path)

                    # 检测是否有标题行
                    has_title, title_text = detect_title_row(df)

                    # 如果有标题，调整数据框（去掉第一行）
                    if has_title:
                        # 保存标题文本
                        actual_title = title_text
                        # 从第二行开始作为数据，第一行作为列名
                        df = pd.read_excel(file_path, skiprows=1)
                        print(f"检测到标题: '{actual_title}'，已跳过第一行")
                    else:
                        actual_title = ""

                    # 生成Sheet名称
                    sheet_name = os.path.splitext(excel_file)[0]
                    if len(sheet_name) > 31:
                        sheet_name = sheet_name[:31]
                        print(
                            f"警告: 文件名 '{excel_file}' 过长，Sheet名称截断为: {sheet_name}"
                        )

                    # 处理重名
                    original_name = sheet_name
                    counter = 1
                    while sheet_name in workbook.sheetnames:
                        sheet_name = f"{original_name}_{counter}"
                        counter += 1

                    # 创建新工作表
                    worksheet = workbook.create_sheet(sheet_name)

                    # 写入数据
                    if has_title:
                        # 写入标题（第一行）
                        worksheet.merge_cells(
                            start_row=1,
                            start_column=1,
                            end_row=1,
                            end_column=len(df.columns),
                        )
                        title_cell = worksheet.cell(row=1, column=1, value=actual_title)

                        # 写入表头（第二行）
                        for col_num, column_title in enumerate(df.columns, 1):
                            cell = worksheet.cell(
                                row=2, column=col_num, value=column_title
                            )

                        # 写入数据（从第三行开始）
                        for row_idx, row in enumerate(df.itertuples(index=False), 3):
                            for col_idx, value in enumerate(row, 1):
                                worksheet.cell(row=row_idx, column=col_idx, value=value)
                    else:
                        # 直接写入数据（包括表头）
                        for row_idx, row in enumerate(df.itertuples(index=False), 1):
                            for col_idx, value in enumerate(row, 1):
                                worksheet.cell(row=row_idx, column=col_idx, value=value)

                    # 应用格式
                    apply_regular_formatting(worksheet, df, has_title, actual_title)

                    print(f"已完成: {excel_file} -> Sheet: {sheet_name}")

                except Exception as e:
                    print(f"处理文件 '{excel_file}' 时出错: {str(e)}")
                    continue

            # 删除默认创建的空工作表
            if "Sheet" in workbook.sheetnames:
                del workbook["Sheet"]

        print(f"\n合并完成！输出文件: {output_path}")
        return True

    except Exception as e:
        print(f"合并过程中发生错误: {str(e)}")
        return False


def merge_excel_files_simple(output_folder, output_filename="合并文件_简洁版.xlsx"):
    """
    简洁版本：使用pandas直接写入，然后应用格式
    """
    try:
        if not os.path.exists(output_folder):
            print(f"错误：文件夹 '{output_folder}' 不存在")
            return False

        excel_files = [
            f
            for f in os.listdir(output_folder)
            if f.endswith((".xlsx", ".xls")) and not f.startswith("~")
        ]

        if not excel_files:
            print(f"在文件夹 '{output_folder}' 中没有找到Excel文件")
            return False

        output_path = os.path.join('output', output_filename)

        with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
            for excel_file in excel_files:
                file_path = os.path.join(output_folder, excel_file)

                try:
                    # 读取数据
                    df = pd.read_excel(file_path)

                    # 检测是否有标题行
                    has_title, title_text = detect_title_row(df)

                    # 如果有标题，调整数据框
                    if has_title:
                        actual_title = title_text
                        df = pd.read_excel(file_path, skiprows=1)
                    else:
                        actual_title = ""

                    # 生成Sheet名称
                    sheet_name = os.path.splitext(excel_file)[0]
                    if len(sheet_name) > 31:
                        sheet_name = sheet_name[:31]

                    # 写入数据
                    df.to_excel(writer, sheet_name=sheet_name, index=False)

                    # 获取工作表并应用格式
                    worksheet = writer.sheets[sheet_name]
                    apply_regular_formatting(worksheet, df, has_title, actual_title)

                    print(f"已完成: {excel_file} -> Sheet: {sheet_name}")

                except Exception as e:
                    print(f"处理文件 '{excel_file}' 时出错: {str(e)}")
                    continue

        print(f"\n合并完成！输出文件: {output_path}")
        return True

    except Exception as e:
        print(f"合并过程中发生错误: {str(e)}")
        return False


# 使用示例
if __name__ == "__main__":
    folder_path = "output"

    print("开始合并Excel文件...")
    success = merge_excel_files_regular(folder_path, "合并文件.xlsx")

    if success:
        print("\n文件合并成功！")
    else:
        print("\n文件合并失败，尝试简洁版本...")
        success = merge_excel_files_simple(folder_path, "合并文件_简洁版.xlsx")
        if success:
            print("\n简洁版本合并成功！")
        else:
            print("\n所有合并尝试均失败！")
